﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using Modellayer;


namespace Dblayer
{
    public class DbReservation
    {
        SqlConnection con;
        DbShow dbS;
        DbPerson dbP;

        public DbReservation()
        {
            con = new SqlConnection("Data Source=balder.ucn.dk;Initial Catalog=dm76_4;Persist Security Info=True;User ID=DM76_4;Password=MaaGodt");
            dbS = new DbShow();
            dbP = new DbPerson();
        }

        public int getReservationsNumber()
        {
            int maxValue = 0;
            try
            {
                if (con == null)
                {
                    con.Open();
                }
                string getMax = @"SELECT MAX(ReservationsId) FROM ShowRoom";
                SqlCommand myCommand = new SqlCommand(getMax, con);
                maxValue = (int)myCommand.ExecuteScalar();
                return maxValue;
            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);

            }
            return maxValue;
        }   

        public void createReservation(Reservation res)
        {
            try
            {
                con.Open();
                string insert = @"insert into Reservation(ReservationsId, ShowId, PhoneNumber) values ('"+ res.reservationsId + "','" + res.show.showId + "','" + res.person.phoneNumber + "')";
                SqlCommand myCommand = new SqlCommand(insert, con);
                myCommand.ExecuteNonQuery();

            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
            }
            finally
            {
                if (con != null)
                {
                    con.Close();
                }
            }
        }

        public Reservation findReservation(string resId)
        {
            Reservation res = new Reservation();
            SqlDataReader rdr = null;
            try
            {
                con.Open();
                string findQuery = @"select * from Reservation where ReservationsId = '" + resId + "'";
                SqlCommand myCommand = new SqlCommand(findQuery, con);
                rdr = myCommand.ExecuteReader();
                while (rdr.Read())
                {
                    res.reservationsId = int.Parse(rdr["ReservationId"].ToString());
                    Show s = dbS.findShow(int.Parse(rdr["ShowId"].ToString()));
                    res.show = s;
                    Person p = dbP.findPerson(int.Parse(rdr["PhoneNumber"].ToString()));
                    res.person = p;
                    
                }


            }
            catch (Exception e)
            {
                Console.WriteLine("{0}", e.Message);
                res = null;
            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }


                if (con != null)
                {
                    con.Close();
                }

            }

            return res;
        }
    }
}
